1. Field of the Invention
The present embodiments are generally related to determining an optimal query plan for a database query by selecting from a cache of plans organized according to density-based clustering.
2. Background Art
Query plan caching has been widely adopted by relational database management systems (RDMBSs). For frequently executed database queries, query plan optimization consumes a significant portion of total database query execution time. It may be beneficial to cache and reuse an optimized query plan because some frequently executed database queries have a high optimization overhead. A query plan is known as a tree of relational algebra operators which indicate information about choice of algorithm as well as resource allocation. An optimal plan is determined by taking into account recent statistics on data previously obtained as well as current system state. Query plan optimization can become a performance bottleneck, particularly for some queries that do not require much time to execute. Plan caching addresses this problem, i.e. query optimization can be shortened if plans of frequently executed queries are cached and reused.
While query optimization may accurately determine a query plan, currently, determination of an accurate query plan may be very inefficient. In conventional plan caching, the generated plan is chosen based on expected values of certain parameters that characterize the query, data and the current state of system. This is known as the least specific cost plan.
At the two extremes of query plan caching, either the query optimizer is invoked blindly for different instantiations of the query template, resulting in poor caching efficiency, or the plan generated at some expected parameter values is cached and blindly reused for all instantiations of the query template, resulting in performance degradation resulting from executing suboptimal plans.
Therefore, what is needed are system, method and computer program product embodiments for parametric plan caching using density-based clustering.